package com.wframe.project.former.dataservices.marketing;

import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.wframe.project.former.dataservices.products.dao.ProductClassDao;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Author on 2017/6/12.
 */
public class BuySendDao extends JBaseDao {
    public BuySendDao() {
        super("wb_promotion_buy_send");
    }

    /**
     * 根据买送促销表的id查询一条买送促销的活动信息
     * @param buySendId
     * @return
     * @throws Exception
     */
    public JdataBean selectBuySendById(int buySendId) throws Exception {
        return getSession().getObject("select s.id buysendid,p.sort_value,s.promotionid,s.buy_number,s.send_number,s.name productname," +
                "p.platform,p.product_val,p.show_sell,p.district_description,p.start_time,p.end_time,p.state,p.time,p.name promotionsname,p.remark,p.description " +
                "from wb_promotion_buy_send s,wb_promotions p where s.promotionid=p.id and s.id=?", new Object[]{buySendId});
    }

    /**
     * 根据查询条件查询商品
     * @param parms
     * @param size
     * @param pageno
     * @return
     * @throws Exception
     */
    public PageResult     queryChooseProduct(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT m.id,m.`name` pname,m.`code`,(select ifnull(pr.amount,0)-ifnull(pr.djamount,0) from  wb_products_prices pr where pr.productid=m.id) productamount from  wb_products m where m.`status`=2";
        List parmlist = new ArrayList();
        String[] codelist = null ;
        String code = "";
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("pname"))) {
                sql += " and m.name like ?";
                parmlist.add("%" + parms.getString("pname") + "%");
            }
            if (StringUtils.isNotBlank(parms.getString("code"))) {
                String codestr=parms.getString("code");
                sql += " and m.code in ("+ codestr +") ";
            }
            if (StringUtils.isNotBlank(parms.getString("classid"))) {
                if ("null".equals(parms.getString("classid"))) {
                    sql += " and m.id not in (select productid from wb_products_and_class)";
                } else {
                    JdataBean clsbean = new ProductClassDao().getBean(parms.getString("classid"));
                    sql += " and m.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
                    parmlist.add(clsbean.getString("code") + ".%");
                    parmlist.add(clsbean.getString("code"));
                }
            }
        }
        sql += " group by m.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    /**
     * 新增商品控销配置表的数据
     * @param bean
     * @return
     * @throws Exception
     */
    public String addControlsalesSingLe(JdataBean bean) throws Exception {
        return getSession().saveReturnKey("wb_promotion_controlsale", bean.getDataMap());
    }

    public void deleteControlsalesAll(String promotionid) throws HibernateException, Exception{
        getSession().delete("delete from wb_promotion_controlsale WHERE promotion_id=?",new Object[]{promotionid});
    }


    public void updatPromotecontrol(String protomctid,int iscontrol) throws Exception{
        getSession().update("update wb_promotions set is_control=? where id=?",new Object[]{iscontrol,protomctid});
    }

    /**
     * 查看已选择的商品
     * @param parms
     * @param size
     * @param pageno
     * @return
     * @throws Exception
     */
    public PageResult checkProduct(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT id,`name` pname,`code`,types,(select (ifnull(pr.amount,0)-ifnull(pr.djamount,0)) from  wb_products_prices pr where pr.id=n.product_val) productamount from(SELECT *from wb_products "
                + "WHERE id in (SELECT DISTINCT(product_val) from wb_promotions WHERE product_type=1 and promotion_type=0))m "
                + "join (SELECT GROUP_CONCAT(case promotion_type when '0' THEN '单品促销' when '1' THEN "
                + "'买送促销' when '2' THEN '满减促销' when '3' THEN '满赠促销' end) types,product_val "
                + "from (SELECT * from wb_promotions WHERE product_type=1 and product_val "
                + "in (SELECT DISTINCT(product_val) from wb_promotions WHERE product_type=1 and promotion_type=1))t "
                + "GROUP BY product_val)n on m.id=n.product_val";
        sql += " ORDER BY m.id DESC";
        return getSession().query(sql, null, size, pageno);
    }

    /**
     * 根据商品id获取商品
     * @param proid
     * @return
     * @throws Exception
     */
    public JdataBean queryproduct(int proid) throws Exception{
        return getSession().getObject("select id,name from wb_products where id=?",new Object[]{proid});
    }

    public JdataBean querySelectedProduct(int id) throws Exception{
        String sql = "SELECT m.id,m.`name` name,m.`code`,(select ifnull(pr.amount,0)-ifnull(pr.djamount,0) from  wb_products_prices pr where pr.productid=m.id) productamount from  wb_products m where m.`status`=2 and m.id=?";
        return getSession().getObject(sql,new Object[]{id});
    }
}
